package controller;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import entity.Banji;
import entity.Student;
import entity.StudentBanjiInfoVO;
import entity.User;
import util.JDBCUtil;

/*
 * JSP+Servlet
 * Servlet:写java的业务逻辑代码
 * JSP:主要用来展示数据
 * 
 * JSP和Servlet都可以在浏览器直接访问
 * 
 * http://localhost:8080/Java201202/student_list.jsp
 * http://localhost:8080/Java201202/student
 * 8080：是tomcat默认端口号
 * 
 * 你继承什么你就是什么，StudenServlet继承了HttpServlet那么我就是一个Servlet
 */
@WebServlet("/student")
public class StudentServlet extends HttpServlet{

	// 访问Servlet时候，默认会访问这个service方法  arg0 arg1
	// Request:请求
	// Response:响应
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// 解决post请求乱码问题   method="post"
		// req.setCharacterEncoding("UTF-8");
		
		// 哪个用户调用这段代码就是这个用户得到自己的Session
//		HttpSession session = req.getSession();
//		User user = (User) session.getAttribute("user");
//		if (user == null) {// 代表这个用户没有登录
//			// 转发操作：用在查找之后转发到jsp页面展示。其余的都可以用重定向
//			resp.sendRedirect(req.getContextPath() + "/login.jsp");
//			return;
//		}
		
		// System.out.println("StudentServlet.service()");
		// ?前面是url路径, ?后面是参数，可以传递多个参数，参数之间使用&隔开
		//  http://localhost:8080/Java201202/student?method=deleteById&id=12
		//  /Java201202/student?method=deleteById&id=12
	    //  /Java201202/student?method=selectAll
		//  /Java201202/student?method=insert
		String method = req.getParameter("method");
		if (method == null || method.equals("")) {
			method = "selectAll";
		}
		switch (method) {
		case "selectAll":
			selectAll(req, resp);
			break;
		case "selectStudentBanjiInfo":
			selectStudentBanjiInfo(req, resp);
			break;
		case "deleteById":
			deleteById(req, resp);
			break;
		case "getStudentInsertPage":
			getStudentInsertPage(req, resp);
			break;
		case "insert":
			insert(req, resp);
			break;
		case "selectById":
			selectById(req, resp);
			break;
		case "update":
			update(req, resp);
			break;
		default:
			break;
		}
	}

	private void selectStudentBanjiInfo(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("StudentServlet.selectStudentBanjiInfo()");
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<StudentBanjiInfoVO> list = new ArrayList<StudentBanjiInfoVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT s.id,s.name,s.age,s.gender,b.name AS banjiName FROM `student` AS s INNER JOIN `banji` AS b ON s.banji_id=b.id";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				String banjiName = resultSet.getString("banjiName");
				// 现在没有任何一个对象（像Student这种）封装这个查询的结果
				
				// Student student = new Student(id, name, age, gender, banjiId);
				StudentBanjiInfoVO studentBnBanjiInfoVO = new StudentBanjiInfoVO(id, name, age, gender, banjiName);
				
				list.add(studentBnBanjiInfoVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		for (StudentBanjiInfoVO studentBanjiInfoVO : list) {
			System.out.println(studentBanjiInfoVO);
		}
		
		req.setAttribute("list", list);
		req.getRequestDispatcher("/student_banji_info_list.jsp").forward(req, resp);
	}

	private void getStudentInsertPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("StudentServlet.selectStudentInsertPage()");
		ArrayList<Banji> list = new ArrayList<Banji>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name` FROM `banji`";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				
				Banji banji = new Banji(id, name);
				list.add(banji);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		for (Banji banji : list) {
			System.out.println(banji);
		}
		
		req.setAttribute("list", list);
		req.getRequestDispatcher("/student_insert.jsp").forward(req, resp);
	}

	private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		System.out.println("StudentServlet.update()");
		String id = req.getParameter("id");
		String name = req.getParameter("name");
		String age = req.getParameter("age");
		String gender = req.getParameter("gender");
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE `student` SET `name`=?,`age`=?,`gender`=?  WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, Integer.parseInt(age));
			preparedStatement.setString(3, gender);
			preparedStatement.setInt(4, Integer.parseInt(id));
			System.out.println(preparedStatement);
			int count = preparedStatement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		resp.sendRedirect(req.getContextPath() + "/student?method=selectAll");
	}

	private void selectById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("StudentServlet.selectById()");
		String selectId = req.getParameter("id");
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Student student = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student` WHERE `id`=?";
			// 预编译
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, Integer.parseInt(selectId));
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			// 打印statement对象可以打印出执行的sql语句
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				
				student = new Student(id, name, age, gender);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		req.setAttribute("student", student);
		req.getRequestDispatcher("/student_update.jsp").forward(req, resp);
	}

	private void insert(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		System.out.println("StudentServlet.insert()");
//		用户名：<input type="text" name="name"/><br/>
//		年龄： <input type="text" name="age"/><br/>
//		性别： <input type="text" name="gender"/><br/>
		// 不管前台的form表单是使用 method="get" 还是 method="post",
		// 只是前台传递参数的方式不一样，后台的处理是一样
		String name = req.getParameter("name");
		String age = req.getParameter("age");
		String gender = req.getParameter("gender");
		String banjiId = req.getParameter("banjiId");
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO `student`(`name`,`age`,`gender`,`banji_id`) VALUES(?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, Integer.parseInt(age));
			preparedStatement.setString(3, gender);
			preparedStatement.setInt(4, Integer.parseInt(banjiId));
			System.out.println(preparedStatement);
			int count = preparedStatement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		
		// 添加完了之后，再去查找所有学生,展示最新的数据
		resp.sendRedirect(req.getContextPath() + "/student?method=selectAll");
	}

	private void selectAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("StudentServlet.selectAll()");
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<Student> list = new ArrayList<Student>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`gender`,`banji_id` FROM `student`";
			// 预编译
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			// 打印statement对象可以打印出执行的sql语句
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				int banjiId = resultSet.getInt("banji_id");
				
				Student student = new Student(id, name, age, gender, banjiId);
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		for (Student student : list) {
			System.out.println(student);
		}
		
		// 1、将数据放到req这个内存中
		req.setAttribute("list", list);
		///2、跳转到student_list.jsp页面展示数据
		// dispatcher: n. 调度员；[计] 调度程序；[计] 分配器
		req.getRequestDispatcher("/student_list.jsp").forward(req, resp);
	}
	
	
	private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		System.out.println("StudentServlet.deleteById()");
		String id = req.getParameter("id");
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM `student` WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			// parameter:参数
			preparedStatement.setInt(1, Integer.parseInt(id));
			System.out.println(preparedStatement);
			int count = preparedStatement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		// 删除完了之后，再去查找所有学生,展示最新的数据
		// resp.sendRedirect("/Java201202/student");
		resp.sendRedirect(req.getContextPath() + "/student?method=selectAll");
	}

}
